package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import vcp.entity.Lot;
import vcp.entity.Site;
import vcp.enums.LotStatus;
import vcp.enums.SiteEnum;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.LotTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;

/**
 * <p>
 * Lot DAO class implementing all relevant queries
 * </p>
 * 
 * @author VCP team
 * 
 */
public class LotDAO implements IGenericDAO <Lot, Site>
{
	/**
	 * finds all damaged lots within given period of time in given site
	 * 
	 * @param startDate
	 * @param endDate
	 * @param site
	 * @return list with damaged lots
	 */
	public List <Lot> findAllBrokenByDatePeriod ( DateTime startDate, DateTime endDate, SiteEnum site )
	{
		List <Lot> listOfLots = new ArrayList <>();

		String query =
				SQLQueries.SELECT_ALL
						+ SQLQueries.FROM + TableNames.LOT
						+ " " + SQLQueries.WHERE
						+ LotTable.LOT_SITE_ID + "=" + site.getSiteID()
						+ " " + SQLQueries.AND
						+ " " + SQLQueries.DATE + "(" + LotTable.BROKE_START_DATE + ")"
						+ SQLQueries.GTE
						+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(startDate) + "\""
						+ " " + SQLQueries.AND
						+ SQLQueries.DATE + "(" + LotTable.BROKE_END_DATE + ")"
						+ SQLQueries.LTE
						+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(endDate) + "\"";
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			SiteDAO siteDAO = new SiteDAO();
			Site parentSite = siteDAO.findByID(site.getSiteID());

			while ( set.next() )
			{
				Lot lot = this.create();

				lot.setParentSite(parentSite);
				lot.setFloor(set.getInt(LotTable.FLOOR));
				lot.setRow(set.getInt(LotTable.ROW));
				lot.setColumn(set.getInt(LotTable.COLUMN));
				lot.setLotStatus(LotStatus.convert(set.getString(LotTable.STATUS)));
				lot.setCarID(set.getInt(LotTable.CAR_ID));
				lot.setReservedBySubscription(set.getInt(LotTable.RESERVED_BY_SUBSCRIPTION));
				lot.setBrokeStartDate(new DateTime(set.getTimestamp(LotTable.BROKE_START_DATE)));
				lot.setBrokeEndDate(new DateTime(set.getTimestamp(LotTable.BROKE_END_DATE)));

				listOfLots.add(lot);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfLots;
	}

	/**
	 * find all reserved lots in site
	 * 
	 * @param site
	 * @return list of reserved lots
	 */
	public List <Lot> findAllReserevedLots ( SiteEnum site )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.RESERVED_BY_SUBSCRIPTION + " is not null");

		List <Lot> listOfLots = new ArrayList <>();
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);

			SiteDAO siteDao = new SiteDAO();
			while ( set.next() )
			{
				Lot lot = this.create();

				lot.setParentSite(siteDao.findByID(set.getInt(LotTable.LOT_SITE_ID)));
				lot.setFloor(set.getInt(LotTable.FLOOR));
				lot.setRow(set.getInt(LotTable.ROW));
				lot.setColumn(set.getInt(LotTable.COLUMN));
				lot.setLotStatus(LotStatus.convert(set.getString(LotTable.STATUS)));
				lot.setCarID(set.getInt(LotTable.CAR_ID));
				lot.setReservedBySubscription(set.getInt(LotTable.RESERVED_BY_SUBSCRIPTION));
				lot.setBrokeStartDate(new DateTime(set.getTimestamp(LotTable.BROKE_START_DATE)));
				lot.setBrokeEndDate(new DateTime(set.getTimestamp(LotTable.BROKE_END_DATE)));

				listOfLots.add(lot);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfLots;
	}

	/**
	 * find lot bi lot id in specific site
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @return instance of found lot
	 */
	public Lot findByID ( int siteID, int floor, int row, int column )
	{
		return this.findByID(this.createComplexID(siteID, floor, row, column));
	}

	/**
	 * find reserved lot by subscription Id
	 * 
	 * @param subscriptionID
	 * @return lot
	 */
	public Lot findByReservedSubscriptionID ( int subscriptionID )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.RESERVED_BY_SUBSCRIPTION + "=" + subscriptionID);

		Lot lot = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);

			SiteDAO siteDao = new SiteDAO();
			if ( set.next() )
			{
				lot = this.create();

				lot.setParentSite(siteDao.findByID(set.getInt(LotTable.LOT_SITE_ID)));
				lot.setFloor(set.getInt(LotTable.FLOOR));
				lot.setRow(set.getInt(LotTable.ROW));
				lot.setColumn(set.getInt(LotTable.COLUMN));
				lot.setLotStatus(LotStatus.convert(set.getString(LotTable.STATUS)));
				lot.setCarID(set.getInt(LotTable.CAR_ID));
				lot.setReservedBySubscription(set.getInt(LotTable.RESERVED_BY_SUBSCRIPTION));
				lot.setBrokeStartDate(new DateTime(set.getTimestamp(LotTable.BROKE_START_DATE)));
				lot.setBrokeEndDate(new DateTime(set.getTimestamp(LotTable.BROKE_END_DATE)));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return lot;
	}

	/**
	 * cheks if car id is parked in given site
	 * 
	 * @param siteID
	 * @param carID
	 * @return true if success
	 */

	public boolean isCarRegisteredOnSiteLot ( int siteID, int carID )
	{
		boolean isRegistered = false;
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.LOT_SITE_ID + "=" + siteID + " " + SQLQueries.AND + LotTable.CAR_ID + "=" + carID);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			isRegistered = set.next();
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return isRegistered;
	}

	/**
	 * deletes lot from site
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @return true if success
	 */
	public boolean deleteByID ( int siteID, int floor, int row, int column )
	{
		return this.deleteByID(this.createComplexID(siteID, floor, row, column));
	}

	/**
	 * updates reserved lot by subscription
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @param subscription
	 * @return true if success
	 */
	public boolean updateReservedBySubscription ( int siteID, int floor, int row, int column, Integer subscription )
	{
		String complexID = this.createComplexID(siteID, floor, row, column);
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.RESERVED_BY_SUBSCRIPTION + "=" + subscription
					, complexID);

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/**
	 * updates lot status
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @param newStatus
	 * @return true if success
	 */
	public boolean updateLotStatusByID ( int siteID, int floor, int row, int column, LotStatus newStatus )
	{
		String complexID = this.createComplexID(siteID, floor, row, column);
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.STATUS + "=" + "\"" + newStatus.toString() + "\""
					, complexID);

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/**
	 * updates lot status to be occupied by parked car
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @param carID
	 * @return true if success
	 */
	public boolean updateCarInLot ( int siteID, int floor, int row, int column, Integer carID )
	{
		String complexID = this.createComplexID(siteID, floor, row, column);
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.CAR_ID + "=" + carID
					, complexID);

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/**
	 * updates the star date of damaged lot
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @param brokeStartDate
	 * @return true if success
	 */
	public boolean updateBrokeStartDate ( Integer siteID, int floor, int row, int column, DateTime brokeStartDate )
	{
		String complexID = this.createComplexID(siteID, floor, row, column);
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.BROKE_START_DATE + "=" + "\"" + new Timestamp(brokeStartDate.getMillis()).toString() + "\""
					, complexID);

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/**
	 * update damaged lot end date
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @param brokeEndDate
	 * @return true if success
	 */
	public boolean updateBrokeEndDate ( Integer siteID, int floor, int row, int column, DateTime brokeEndDate )
	{
		String complexID = this.createComplexID(siteID, floor, row, column);
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.BROKE_END_DATE + "=" + "\"" + new Timestamp(brokeEndDate.getMillis()).toString() + "\""
					, complexID);

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/**
	 * create lot Id
	 * 
	 * @param siteID
	 * @param floor
	 * @param row
	 * @param column
	 * @return mixed ID for lot
	 */
	private String createComplexID ( int siteID, int floor, int row, int column )
	{
		return String.format(
			LotTable.LOT_SITE_ID + "=%d "
					+ SQLQueries.AND
					+ LotTable.FLOOR + "=%d "
					+ SQLQueries.AND
					+ LotTable.ROW + "=%d "
					+ SQLQueries.AND
					+ LotTable.COLUMN + "=%d"
			, siteID
			, floor
			, row
			, column);
	}

	/****************************************************************************************************************************
	 *** IGenericDAO methods ***
	 ****************************************************************************************************************************/

	/**
	 * Not implemented
	 */
	@Override
	public Integer findMaxID ()
	{
		return null;
	}

	@Override
	public Lot create ()
	{
		return new Lot();
	}

	/**
	 * not implemented
	 */
	@Override
	public Lot findByID ( int ID )
	{
		return null;
	}

	/**
	 * not implemented
	 */
	@Override
	public Lot findByEntity ( Site entity )
	{
		return null;
	}

	@Override
	public Lot findByID ( String ID )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.LOT, ID);

		Lot lot = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);

			SiteDAO siteDao = new SiteDAO();
			if ( set.next() )
			{
				lot = this.create();

				lot.setParentSite(siteDao.findByID(set.getInt(LotTable.LOT_SITE_ID)));
				lot.setFloor(set.getInt(LotTable.FLOOR));
				lot.setRow(set.getInt(LotTable.ROW));
				lot.setColumn(set.getInt(LotTable.COLUMN));
				lot.setLotStatus(LotStatus.convert(set.getString(LotTable.STATUS)));
				lot.setCarID(set.getInt(LotTable.CAR_ID));
				lot.setReservedBySubscription(set.getInt(LotTable.RESERVED_BY_SUBSCRIPTION));
				lot.setBrokeStartDate(new DateTime(set.getTimestamp(LotTable.BROKE_START_DATE)));
				lot.setBrokeEndDate(new DateTime(set.getTimestamp(LotTable.BROKE_END_DATE)));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return lot;
	}

	/**
	 * finds lot by car id
	 * 
	 * @param ID
	 * @return specific lot by ID
	 */
	public Lot findByCar ( int ID )
	{
		String query = SQLQueries.SELECT_ALL + " " + SQLQueries.FROM + TableNames.LOT + " " + SQLQueries.WHERE + LotTable.CAR_ID + "=" + ID;

		Lot lot = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);

			SiteDAO siteDao = new SiteDAO();
			if ( set.next() )
			{
				lot = this.create();

				lot.setParentSite(siteDao.findByID(set.getInt(LotTable.LOT_SITE_ID)));
				lot.setFloor(set.getInt(LotTable.FLOOR));
				lot.setRow(set.getInt(LotTable.ROW));
				lot.setColumn(set.getInt(LotTable.COLUMN));
				lot.setLotStatus(LotStatus.convert(set.getString(LotTable.STATUS)));
				lot.setCarID(set.getInt(LotTable.CAR_ID));
				lot.setReservedBySubscription(set.getInt(LotTable.RESERVED_BY_SUBSCRIPTION));
				lot.setBrokeStartDate(new DateTime(set.getTimestamp(LotTable.BROKE_START_DATE)));
				lot.setBrokeEndDate(new DateTime(set.getTimestamp(LotTable.BROKE_END_DATE)));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return lot;
	}

	@Override
	public List <Lot> findAll ( Site parentSite )
	{
		List <Lot> listOfLots = null;

		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.LOT
					, LotTable.LOT_SITE_ID + "=" + String.valueOf(parentSite.getSiteID()));
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);

			listOfLots = new ArrayList <Lot>();

			while ( set.next() )
			{
				Lot lot = this.create();

				lot.setParentSite(parentSite);
				lot.setFloor(set.getInt(LotTable.FLOOR));
				lot.setRow(set.getInt(LotTable.ROW));
				lot.setColumn(set.getInt(LotTable.COLUMN));
				lot.setLotStatus(LotStatus.convert(set.getString(LotTable.STATUS)));
				lot.setCarID(set.getInt(LotTable.CAR_ID));
				lot.setReservedBySubscription(set.getInt(LotTable.RESERVED_BY_SUBSCRIPTION));
				lot.setBrokeStartDate(new DateTime(set.getTimestamp(LotTable.BROKE_START_DATE)));
				lot.setBrokeEndDate(new DateTime(set.getTimestamp(LotTable.BROKE_END_DATE)));

				listOfLots.add(lot);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfLots;
	}

	@Override
	public boolean save ( Lot entity )
	{
		boolean result = false;
		String valuesSet =
				String.format(
					"%d, %d, %d, %d,\"%s\", %d, %d,\"%s\",\"%s\"",
					entity.getParentSite().getSiteID(),
					entity.getFloor(),
					entity.getRow(),
					entity.getColumn(),
					entity.getStatusName(),
					entity.getCarID(),
					entity.getReservedBySubscription(),
					entity.getBrokeStartDate() != null
														? new Timestamp(entity.getBrokeStartDate().getMillis())
														: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()),
					entity.getBrokeEndDate() != null
													? new Timestamp(entity.getBrokeEndDate().getMillis())
													: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()));

		String sqlQuery =
				String.format(
					SQLQueries.INSERT_INTO__TABLENAME__VALUES__
					, TableNames.LOT
					, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return success;
	}

	/**
	 * not implemented
	 */
	@Override
	public boolean deleteByID ( int ID )
	{
		return false;
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		String sqlQuery =
				String.format(
					SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_
					, TableNames.LOT
					, ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return success;
	}

	/**
	 * testing queries functionality
	 * 
	 * @param main
	 */
	public static void main ( String[] main )
	{
		System.out.println("******************TESTING LOTDAO***************");
		LotDAO dao = new LotDAO();
		Lot testLot = dao.create();

		SiteDAO siteDAO = new SiteDAO();
		Site parentSite = siteDAO.create();

		parentSite.setSite(SiteEnum.SITE_1);
		siteDAO.save(parentSite);

		testLot.setColumn(5);
		testLot.setFloor(2);
		testLot.setRow(2);
		testLot.setParentSite(parentSite);
		testLot.setLotStatus(LotStatus.NON_WORKING);
		testLot.setCarID(113);

		dao.save(testLot);
		// System.out.println("1 lot was saved");
		// System.out.println("*********************************************************");
		//
		// testLot = dao.create();
		// testLot.setColumn(8);
		// testLot.setFloor(4);
		// testLot.setRow(1);
		// testLot.setParentSite(parentSite);
		// testLot.setLotStatus(LotStatus.RESERVE);
		//
		// dao.save(testLot);
		// System.out.println("2 lot was saved");
		// System.out.println("*********************************************************");
		//
		// testLot = dao.findByID(parentSite.getSiteID(), 2, 2, 5);
		// System.out.println("lot with id:" + "floor:" + testLot.getFloor() +
		// "; row:" + testLot.getRow() + "; column:" + testLot.getColumn() +
		// "; parentID:" + testLot.getParentSite().getSiteID() + " found");
		// System.out.println("*********************************************************");
		//
		// for ( Lot foundLot : dao.findAll(parentSite) )
		// {
		// System.out.println("lot with id:" + "floor:" + foundLot.getFloor() +
		// "; row:" + foundLot.getRow() + "; column:" + foundLot.getColumn() +
		// "; parentID:" + foundLot.getParentSite().getSiteID() + " found");
		// }
		//
		// System.out.println("*********************************************************");
		//
		// dao.updateLotStatusByID(parentSite.getSiteID(), 4, 1, 8,
		// LotStatus.OCCUPIED);
		// System.out.println("lot with id:" + "floor:" + testLot.getFloor() +
		// "; row:" + testLot.getRow() + "; column:" + testLot.getColumn() +
		// "; parentID:" + testLot.getParentSite().getSiteID() +
		// " change status");
		//
		// System.out.println("*********************************************************");
		// try
		// {
		// Thread.sleep(5000);
		// }
		// catch ( InterruptedException e )
		// {
		// e.printStackTrace();
		// }
		// System.out.println("*********************************************************");
		//
		// dao.deleteByID(parentSite.getSiteID(), 4, 1, 8);
		// dao.deleteByID(parentSite.getSiteID(), 2, 2, 5);
		//
		// System.out.println("lots deleted");
	}
}
